package cn.ainit.sys.models;

import java.io.BufferedReader;
import java.io.Reader;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpSession;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.ainit.common.ReturnObject;
import cn.ainit.sys.common.dao.SysBaseDao;
import cn.ainit.sys.dz.entity.SysDzSource;
import cn.ainit.sys.framework.entity.ComButtons;
import cn.ainit.sys.framework.entity.SysButtons;
import cn.ainit.sys.framework.entity.SysColumns;
import cn.ainit.sys.menu.SessionCont;
import cn.ainit.sys.print.entity.SysPrint;
import cn.ainit.sys.query.datastore.DataStore;
@Transactional
@Repository("singleDao")
public class SingleDaoImpl extends SysBaseDao implements SingleDao {

	@Override
	public List butnList(String butnId) throws Exception {
		String sql = "SELECT S.CONFIG_CONT,S.BUT_RIGHT,S.BUT_CODE,S.O_ID_MODIFY,S.LAST_MOD_DATE,S.O_ID_INPUT,S.INPUT_DATE,"+
		" S.SCRIPT_FUNC, S.URL,S.ISSHOW,S.HEIGHT,S.WIDTH,S.Y,S.X,S.RIGHT_ID, S.RIGHTS,S.ORDERNUM,S.BUTN_TYPE, " +
		" S.BUTN_CNAME,S.BUTN_NAME,S.NAME,S.DS_ID, S.IS_GROUP,S.GROUP_ID" +
		" FROM SYS_BUTNS S  WHERE S.ISSHOW = 'T' AND S.DS_ID = '"+butnId+"' order by S.ORDERNUM";
		List list = new ArrayList();
		list = this.getJdbcTemplate().queryForList(sql);
		return list ;
	}
	@Override
	public List butnListRight(String butnId,String dsId,String oId,String flag) throws Exception {
		String sql ="SELECT S.CONFIG_CONT,S.BUT_RIGHT,S.BUT_CODE,S.O_ID_MODIFY,S.LAST_MOD_DATE,S.O_ID_INPUT,S.INPUT_DATE,"+
		"S.MEMO,S.WIDTH,S.ICON,S.SCRIPT_FUNC, S.URL,S.ISSHOW,S.HEIGHT,S.ICONCLS,S.WIDTH" +
		",S.Y,S.X,S.RIGHT_ID, S.RIGHTS,S.ORDERNUM,S.BUTN_TYPE, S.BUTN_CNAME,S.BUTN_NAME,S.NAME,S.DS_ID, "+
		"S.IS_GROUP,S.GROUP_ID "+
		" FROM SYS_BUTNS S  WHERE S.ISSHOW = 'T' AND S.DS_ID = '"+butnId+"'  AND S.PAGE_TYPE = '"+flag+"' order by S.ORDERNUM";
		List list = new ArrayList();
		list = this.getJdbcTemplate().queryForList(sql);
		return list;
	}

	@Override
	public List<SysButtons> butnListRightTop(String butnId, String dsId, String oId) throws Exception{
		String sql ="SELECT S.CONFIG_CONT,S.BUT_RIGHT,S.BUT_CODE,S.O_ID_MODIFY,S.LAST_MOD_DATE,S.O_ID_INPUT,S.INPUT_DATE,"+
		"S.MEMO,S.ICON, PK_SYS_UI.uf_right_ButtonFunc('"+dsId+"','','"+oId+"',S.BUT_CODE,S.SCRIPT_FUNC) SCRIPT_FUNC, S.URL,"+
		"S.ISSHOW,S.HEIGHT,S.WIDTH,S.Y,S.X,S.RIGHT_ID, S.RIGHTS,S.ORDERNUM,S.BUTN_TYPE, S.BUTN_CNAME,S.BUTN_NAME,S.NAME,S.DS_ID "+
		" FROM SYS_BUTNS S  WHERE S.ISSHOW = 'T'  and S.IS_BELOW='F' AND S.DS_ID = '"+dsId+"' order by S.ORDERNUM";
		List list = new ArrayList();
		list = this.getJdbcTemplate().queryForList(sql);
		return list;
	}

	@Override
	public List<ComButtons> combutnList(String butnId) throws Exception {
		String hql = "from ComButtons btn where btn.show = 'T' and btn.dsid = ? order by btn.orderNum";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, butnId);
		return (List<ComButtons>)query.list();
	}

	@Override
	public List combutnListRight(String butnId,String dsId,String oId) throws Exception {
		String sql = "SELECT S.CONFIG_CONT,S.BUT_RIGHT,S.BUT_CODE,S.O_ID_MODIFY,S.LAST_MOD_DATE,S.O_ID_INPUT,S.INPUT_DATE,"+
		" S.MEMO,S.WIDTH,S.ICON,S.SCRIPT_FUNC,S.URL,S.ISSHOW,S.ICONCLS,S.HEIGHT,S.WIDTH,S.Y,S.X,S.RIGHT_ID, S.RIGHTS,S.ORDERNUM,S.BUTN_TYPE, " +
		" S.BUTN_CNAME,S.BUTN_NAME,S.NAME,S.DS_ID,S.IS_GROUP,S.GROUP_ID FROM SYS_BUTTONS S  " +
		" WHERE S.ISSHOW = 'T' AND S.DS_ID = '"+butnId+"' order by S.ORDERNUM  ";
		List list = new ArrayList();
		list = this.getJdbcTemplate().queryForList(sql);
		return list;
	}


	@Override
	public DataStore getDataStore(String dsId) throws Exception {
		String hql = " from DataStore ds where ds.dsId = ?";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, dsId);
		List<DataStore> list = query.list();
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}
	@Override
	public List queryRowBySql(String sqlText) throws Exception {
		List queryRow = new ArrayList();
		try {
			queryRow = this.getJdbcTemplate().queryForList(sqlText);
		} catch (Exception e) {
			throw new RuntimeException("读取数据，字段可能不存在["+e.getMessage()+"]，请检查Sys_columns.NAME中的字段和表中的sql是否一致");
		}
		return queryRow;
	}

	@Override
	public List<SysColumns> loadColumns(String colId, String type)
	throws Exception {
		Query query = this.getSessionFactory().getCurrentSession().createQuery("from SysColumns where dsid = '"+colId+"'");
		List<SysColumns> columns = query.list();
		return columns;
	}

	@Override
	public List<SysColumns> findFksByDsid(String dsId) throws Exception {
		String  hql = "from SysColumns sc where sc.isFK = 'T' and dsid = '"+dsId+"'";
		return this.getSessionFactory().getCurrentSession().createQuery(hql).list();
	}
	@Override
	public List<Map<String, Object>> findPksByDsid(String dsId) throws Exception {
		//    	String  hql = "from SysColumns sc where sc.isPK = 'T' and dsid = '"+dsId+"'";

		String sql = "SELECT * FROM SYS_COLUMNS S WHERE S.IS_PK = 'T' AND S.DS_ID = '"+dsId+"'";
		return this.jdbcTemplate.queryForList(sql);
	}
	@Override
	public List loadDzInfo(String dsId) throws Exception {
		// //返回的对象集合   【0】 对象   【1】 编码
		List objAcode = new ArrayList();
		//		//对象集合
		List objs = new ArrayList();
		//编码集合
		List codeNums = new ArrayList();
		SysDzSource source = new SysDzSource();
		List<SysColumns> sysColumns = sessionFactory.getCurrentSession().createQuery("from SysColumns sc where sc.dsid = ?").setString(0, dsId).list();
		String sql = "";
		//" select "+showPk+" id,"+showName+" name from ("+sqlText+" ) TMP";
		for (int i = 0; i < sysColumns.size(); i++) {
			if (sysColumns.get(i).getEdiType() != null && !sysColumns.get(i).getEdiType().equals("")) {
				source = (SysDzSource) sessionFactory.getCurrentSession().load(SysDzSource.class, sysColumns.get(i).getEdiType());
				//加载对照信息
				if (source == null ) {
					throw new RuntimeException("对照信息没有配置:找不到["+sysColumns.get(i).getEdiType()+"]对照信息,请配置对照信息");
				}
				sql = " select "+source.getShowPk()+" id,"+source.getShowName()+" name from ("+source.getSqlText()+" ) TMP";
				objs.add(this.getJdbcTemplate().queryForList(sql));
				//存放编码
				codeNums.add(source.getDzCodeNumber());
			}
		}
		objAcode.add(objs);
		objAcode.add(codeNums);
		return objAcode;
	}

	@Override
	public String formatSql(String dsId, String sqlText, String uid)
	throws Exception {
		return this.formatDataSql(dsId, sqlText, uid);
	}

	@Override
	public List<SysButtons> combutnListT(String dsid) throws Exception {
		String sql = "select but_group_id from SYS_BUTNS_map where ds_id = '"+dsid+"'";
		List<Map<String, Object>> comid = this.getJdbcTemplate().queryForList(sql);
		StringBuffer butidstr = new StringBuffer();
		for(int i = 0; i < comid.size(); i++){
			butidstr.append("'"+comid.get(i).get("BUT_GROUP_ID").toString()+"'");
			if (i < comid.size() - 1) {
				butidstr.append(",");
			}
		}
		System.out.println(dsid+"==="+sql);
		List<SysButtons> comButtons = new ArrayList<SysButtons>();
		if (comid == null || comid.isEmpty()) {
			return null;
		}else {
			String hql = "from SysButtons cb where cb.dsid  in ("+ butidstr.toString() +")";
			System.out.println("测试按钮通用  2"+hql);
			comButtons = this.getSessionFactory().getCurrentSession().createQuery(hql).list();
		}
		return comButtons;
	}

	@Override
	public String formatRowSql(String dsId, String sqlText, String uid)
	throws Exception {
		return this.formatRowDataSql(dsId, sqlText, uid);
	}
	@Override
	public List<SysPrint> printTypeList(String dsId) throws Exception {
		String hql="from SysPrint where id.dsId=?";
		Query query = this.getSessionFactory().getCurrentSession().createQuery(hql);
		query.setString(0, dsId);
		List<SysPrint> list=query.list();
		return list;
	}
	@Override
	public String dcExcel(String title, String url, String[] topName,
			String[] dbCellName, List<Map<String, String>> list)
	throws Exception {
		try {
			this.exportEmployeeByPoi(title, url, topName, dbCellName, list);
			return "success";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "fail";
	}
	@Override
	public String formatTranSql(String dsId, String sqlText, String uid)
	throws Exception {
		return this.formatSqlTra(dsId, sqlText, uid);
	}
		
	
	@Override
	public boolean getUpdateInfo(List<String> sqls) throws Exception {
		Connection conn = null;
		Statement stmt=null;
		boolean flag=true;
		try {
			conn =  this.getJdbcTemplate().getDataSource().getConnection();
			stmt=conn.createStatement();
			conn.setAutoCommit(false);
			for(String  sql : sqls){
				stmt.addBatch(sql);
			}
			int[] updateCounts = stmt.executeBatch();
			conn.setAutoCommit(true);               
		} catch (Exception e) {
			System.out.println("=============================批量数据太大导入数据失败");
			e.printStackTrace();
			flag=false;
		}finally{
			if(stmt!=null){
				stmt.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return flag;
	}
	
	
	@Override
	public boolean getUpdateInfo_s(String sql) {
         try {
			getJdbcTemplate().execute(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	@Override
	public String getimpFuc(String flg, String dsid,
			String kvs, String hIdMatch) throws Exception {
		String JsonStr = "";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			 conn= this.getJdbcTemplate().getDataSource().getConnection();
			 String sql = "{Call PK_IMP.UP_IMPMATCH(?,?,?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,hIdMatch);
			 cs.setString(2,dsid);
			 cs.setString(3, kvs);
			 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CLOB);
			 try {
				 cs.execute();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
			     Clob clob = cs.getClob(4);
			     Reader is = clob.getCharacterStream();// 得到流
			     BufferedReader br = new BufferedReader(is);
			     String s = br.readLine();
			     StringBuffer sb = new StringBuffer();
			     while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
			     sb.append(s);
			     s = br.readLine();
			     }
			     JsonStr = sb.toString();
			     System.out.println(1111);
			     if(is!=null){
			    	 is.close();
			     }
			} catch (Exception e) {
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (cs != null) {
				cs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return JsonStr;
	}
	
	@Override
	public String getimpFucZT(String flg, String dsid,
			String kvs, String hIdMatch) throws Exception {
		String JsonStr = "";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			 conn= this.getJdbcTemplate().getDataSource().getConnection();
			 String sql = "{Call PK_IMP.UP_IMPMATCH_ZT(?,?,?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,hIdMatch);
			 cs.setString(2,dsid);
			 cs.setString(3, kvs);
			 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CLOB);
			 try {
				 cs.execute();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
			     Clob clob = cs.getClob(4);
			     Reader is = clob.getCharacterStream();// 得到流
			     BufferedReader br = new BufferedReader(is);
			     String s = br.readLine();
			     StringBuffer sb = new StringBuffer();
			     while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
			     sb.append(s);
			     s = br.readLine();
			     }    
			     JsonStr = sb.toString();
			     if(is!=null){
			    	 is.close();
			     }
			} catch (Exception e) {
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (cs != null) {
				cs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return JsonStr;
	}

	@Override
	public String getimpFucCYXH(String flg, String dsid,
							  String kvs, String hIdMatch) throws Exception {
		String JsonStr = "";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			String sql = "{Call PK_IMP.UP_IMPMATCH_CYXH(?,?,?,?)}";
			cs = conn.prepareCall(sql);
			cs.setString(1,hIdMatch);
			cs.setString(2,dsid);
			cs.setString(3, kvs);
			cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CLOB);
			try {
				cs.execute();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				Clob clob = cs.getClob(4);
				Reader is = clob.getCharacterStream();// 得到流
				BufferedReader br = new BufferedReader(is);
				String s = br.readLine();
				StringBuffer sb = new StringBuffer();
				while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
					sb.append(s);
					s = br.readLine();
				}
				JsonStr = sb.toString();
				if(is!=null){
					is.close();
				}
			} catch (Exception e) {
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (cs != null) {
				cs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return JsonStr;
	}

	@Override
	public String getimpFucCw(String flg, String dsid,
							  String kvs, String hIdMatch) throws Exception {
		String JsonStr = "";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			String sql = "{Call CW_IMP.UP_IMPMATCH(?,?,?,?)}";
			cs = conn.prepareCall(sql);
			cs.setString(1,hIdMatch);
			cs.setString(2,dsid);
			cs.setString(3, kvs);
			cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CLOB);
			try {
				cs.execute();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				Clob clob = cs.getClob(4);
				Reader is = clob.getCharacterStream();// 得到流
				BufferedReader br = new BufferedReader(is);
				String s = br.readLine();
				StringBuffer sb = new StringBuffer();
				while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
					sb.append(s);
					s = br.readLine();
				}
				JsonStr = sb.toString();
				if(is!=null){
					is.close();
				}
			} catch (Exception e) {
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (cs != null) {
				cs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return JsonStr;
	}
	
	//活动费用到存储过程
	@Override
	public String getimpFucFee(String flg, String dsid,
			String kvs, String hIdMatch) throws Exception {
		String JsonStr = "";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			 conn= this.getJdbcTemplate().getDataSource().getConnection();
			 String sql = "{Call FEE_IMP.UP_IMPMATCH(?,?,?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,hIdMatch);
			 cs.setString(2,dsid);
			 cs.setString(3, kvs);
			 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CLOB);
			 try {
				 cs.execute();
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
			     Clob clob = cs.getClob(4);
			     Reader is = clob.getCharacterStream();// 得到流
			     BufferedReader br = new BufferedReader(is);
			     String s = br.readLine();
			     StringBuffer sb = new StringBuffer();
			     while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
			     sb.append(s);
			     s = br.readLine();
			     }    
			     JsonStr = sb.toString();
			     if(is!=null){
			    	 is.close();
			     }
			} catch (Exception e) {
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (cs != null) {
				cs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return JsonStr;
	}
	/**************************************************
	 * 刘振涛 2015年11月26日19:32:11
	 * 执行通用的存储过程
	 **************************************************/
	@Override
	public String CommonImpProc(String dsid,String pici,String uid)throws Exception{
		String returnStr = "";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			 conn= this.getJdbcTemplate().getDataSource().getConnection();
			 String sql = "{Call PK_IMP.UP_ComIMP(?,?,?,?)}";
			 cs = conn.prepareCall(sql);
			 cs.setString(1,pici);
			 cs.setString(2,dsid);
			 cs.setString(3,uid);
			 cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CLOB);
			 cs.execute();
		     Clob clob = cs.getClob(4);
		     Reader is = clob.getCharacterStream();// 得到流
		     BufferedReader br = new BufferedReader(is);
		     String s = br.readLine();
		     StringBuffer sb = new StringBuffer();
		     while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
		     sb.append(s);
		     s = br.readLine();
		     }
		     returnStr = sb.toString();
		     if(is!=null){
		    	 is.close();
		     }
			 
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (cs != null) {
				cs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return returnStr;
	}
	
	@Override
	public List<Map<String, Object>> findBySql(String sql) {
		return getJdbcTemplate().queryForList(sql);

	}
	@Override
	public String dbclickRight(String dsId, String pkVal, String manId)
	throws Exception {
		String str = "";
		Connection conn= null;
		CallableStatement cs = null;
		try {
			conn= this.getJdbcTemplate().getDataSource().getConnection();
			String sql = "{Call  PK_TEST.up_dblclickRight(?,?,?,?)}";
			cs = conn.prepareCall(sql);
			cs.setString(1,dsId);
			cs.setString(2,pkVal);
			cs.setString(3, manId);
			cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
			cs.execute();
			str = cs.getString(4);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (cs != null) {
				cs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return str;
	}
	
	
	@Override
	public List returnNCopyList(String mxDsId) throws Exception {
		List ncopyList=new ArrayList();
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		String noCopySql=" SELECT s.name FROM sys_columns s WHERE s.ds_id='"+mxDsId+"'  AND ( s.is_copy!='T' OR s.is_copy IS NULL)";
		list = this.getJdbcTemplate().queryForList(noCopySql);
		if(list!=null){
			for (int i = 0; i < list.size(); i++) {
				ncopyList.add(list.get(i).get("NAME"));
			}
		}
		return ncopyList;
	}
	
	
	@Override
	public List<Map<String, Object>> queryProduct(String H_ISBN, String H_NAME) {
		 String sql = "select * from  BS_PRODUCT where 1=1";
		 if(!"".equals(H_ISBN) && H_ISBN != null){
			 sql +=" and H_ISBN LIKE '%"+ H_ISBN +"%'";
		 }
		 if(!"".equals(H_NAME) && H_NAME != null){
			 sql +=" and H_NAME LIKE '%"+ H_ISBN +"%'";
		 }
		 List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
		return list;
	}
	
	@Override
	public ReturnObject setFxPl(String pkName,String zDsId,DataStore dataStore,String dsId,String djId, String plFlag,JSONObject plObj, JSONArray plRows,
			HttpSession session) throws Exception {
		ReturnObject reObject=new ReturnObject();
		String  stockId=plObj.getString("STOCK_ID");     //库房
		String  hAmount=plObj.getString("H_AMOUNT");     //数量
		String  hDiscount=plObj.getString("H_DISCOUNT"); // 折扣
		String  shelfId=plObj.getString("SHELF_ID");     //架号
		String updateTable=dataStore.getUpdateTable();  //获取指定的表
		String pjConfigSql="";
		List<String> list=new ArrayList();
		if(stockId!=null&&!stockId.equals("null")&&stockId!=""){
			pjConfigSql+=" STOCK_ID='"+stockId+"',";
		}
		if(hAmount!=null&&!hAmount.equals("null")&&hAmount!=""){
			pjConfigSql+=" H_AMOUNT='"+hAmount+"',";
		}
		if(hDiscount!=null&&!hDiscount.equals("null")&&hDiscount!=""){
			pjConfigSql+=" H_DISCOUNT='"+hDiscount+"',";
		}
		if(shelfId!=null&&!shelfId.equals("null")&&shelfId!=""){
			pjConfigSql+=" SHELF_ID='"+shelfId+"' ,";
		}
		Integer lastIndex=pjConfigSql.lastIndexOf(",");
		pjConfigSql=pjConfigSql.substring(0,lastIndex);
		
		StringBuffer updateSql=new StringBuffer("update  "+updateTable+" set "+pjConfigSql+" where "+pkName+" in(");
		String tempsql="";
		//更新指定的行，然后调用保存的后置方法
		for (int i = 0; i < plRows.size(); i++) {
			JSONObject json=plRows.getJSONObject(i);
			String pkId=json.getString(pkName);
			if(i== plRows.size()-1){
				tempsql+="'"+pkId+"')";
				
			}else{
				tempsql+="'"+pkId+"',";
			}
		}
		updateSql.append(tempsql);
		Connection conn = null;
		PreparedStatement stmt = null;
		CallableStatement cstmt = null;
		conn = this.getJdbcTemplate().getDataSource().getConnection();
		stmt = conn.prepareStatement(updateSql.toString());
		try {
			stmt.executeUpdate();
			//调用保存的后置过程
			//执行存储过程
			//执行存储过程
			String queryProSql = " SELECT DS_ID,PREFIX,BEFORE_PROC,AFTER_PROC,MEMO FROM SYS_SAVE_PROCEDURES where DS_ID = '"+zDsId+"'";
			List<Map<String, Object>> procesListM = this.getJdbcTemplate().queryForList(queryProSql);
			Object proc = null;
			if (procesListM != null && !procesListM.isEmpty()) {
				proc = procesListM.get(0).get("AFTER_PROC");
				if (proc != null && !"".equals(proc)) {
					String procsql = "{Call "+proc+"}";
					cstmt = conn.prepareCall(procsql);
					cstmt.setString(1,djId);
					cstmt.setString(2,zDsId);
					cstmt.setString(3, "");
					cstmt.setString(4,session.getAttribute(SessionCont.uid) == null?"":session.getAttribute(SessionCont.uid).toString());
					cstmt.registerOutParameter(5,oracle.jdbc.OracleTypes.VARCHAR);
					cstmt.execute();
					reObject.setInfo(cstmt.getString(5));
					reObject.setMsg("success");
				}
			}else{
				reObject.setInfo("本单据没有后置的计算过程！请添加后置过程，防止出现批量的时候没有计算的过程");
				reObject.setMsg("error");
			}
		} catch (Exception e) {
			e.printStackTrace();
			reObject.setMsg("error");
		}finally{
			if (cstmt != null) {
				cstmt.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn!=null) {
				conn.close();
			}
		}
		return reObject;
	}

    @Override
    public int sureSaveisnull(String NAME,String ccid){
        String sql = "select count(1) from bs_clients b where b.CLIENTS_NAME='" + NAME + "' and b.CC_ID='" + ccid + "'";
        int r = this.getJdbcTemplate().queryForInt(sql);
        return r;
    }

	@Override
	public int cwygrkSaveisnull(String H_ID,String YGR_ID){
		String sql = "select count(1) from cw_InstockMoney c where c.H_ID='" + H_ID +"' and c.ygr_id not in (select cc.ygr_id from cw_InstockMoney cc where cc.ygr_id='"+YGR_ID+"')";
		int r = this.getJdbcTemplate().queryForInt(sql);
		return r;
	}

    //客户实销上报（按日）到存储过程
    @Override
    public String getimpFucFC(String flg, String dsid,
                               String kvs, String hIdMatch) throws Exception {
        String JsonStr = "";
        Connection conn= null;
        CallableStatement cs = null;
        try {
            conn= this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call FX_IMP.UP_IMP_SALESDAY(?,?,?,?)}";
            cs = conn.prepareCall(sql);
            cs.setString(1,hIdMatch);
            cs.setString(2,dsid);
            cs.setString(3, kvs);
            cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CLOB);
            try {
                cs.execute();
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                Clob clob = cs.getClob(4);
                Reader is = clob.getCharacterStream();// 得到流
                BufferedReader br = new BufferedReader(is);
                String s = br.readLine();
                StringBuffer sb = new StringBuffer();
                while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
                    sb.append(s);
                    s = br.readLine();
                }
                JsonStr = sb.toString();
                if(is!=null){
                    is.close();
                }
            } catch (Exception e) {
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if (cs != null) {
                cs.close();
            }
            if(conn != null){
                conn.close();
            }
        }
        return JsonStr;
    }


    @Override
    public List<Map<String, Object>> queryProductBYDAY(String H_ISBN, String H_NAME) {
        String sql = "select H_ISBN,H_NAME,H_PRICE from  BS_PRODUCT where 1=1";
        if(!"".equals(H_ISBN) && H_ISBN != null){
            sql +=" and H_ISBN LIKE '%"+ H_ISBN +"%'";
        }
        if(!"".equals(H_NAME) && H_NAME != null){
            sql +=" and H_NAME LIKE '%"+ H_NAME +"%'";
        }
        sql +=" UNION ALL select GROUP_NUMBER,GROUP_NAME,H_PRICE from FX_GROUPMAINTAIN where is_verify='1' and is_destroy='0' and 1=1";
        if(!"".equals(H_ISBN) && H_ISBN != null){
            sql +=" and GROUP_NUMBER LIKE '%"+ H_ISBN +"%'";
        }
        if(!"".equals(H_NAME) && H_NAME != null){
            sql +=" and GROUP_NAME LIKE '%"+ H_NAME +"%'";
        }
        List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
        return list;
    }

    //客户实销上报（按月）到存储过程
    @Override
    public String getimpFucFCByMonth(String flg, String dsid,
                              String kvs, String hIdMatch) throws Exception {
        String JsonStr = "";
        Connection conn= null;
        CallableStatement cs = null;
        try {
            conn= this.getJdbcTemplate().getDataSource().getConnection();
            String sql = "{Call FX_IMP.UP_IMP_SALESMonth(?,?,?,?)}";
            cs = conn.prepareCall(sql);
            cs.setString(1,hIdMatch);
            cs.setString(2,dsid);
            cs.setString(3, kvs);
            cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CLOB);
            try {
                cs.execute();
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                Clob clob = cs.getClob(4);
                Reader is = clob.getCharacterStream();// 得到流
                BufferedReader br = new BufferedReader(is);
                String s = br.readLine();
                StringBuffer sb = new StringBuffer();
                while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
                    sb.append(s);
                    s = br.readLine();
                }
                JsonStr = sb.toString();
                if(is!=null){
                    is.close();
                }
            } catch (Exception e) {
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if (cs != null) {
                cs.close();
            }
            if(conn != null){
                conn.close();
            }
        }
        return JsonStr;
    }
}
